from odoo import models, fields, api, _
from odoo.exceptions import UserError, ValidationError
import base64
import pandas as pd


# 属性导入
class ProAttributeImport(models.Model):
    _name = 'sb.pro.attribute.import'
    _description = 'Pro Attribute Import'

    name = fields.Char(string="Name")
    file_name = fields.Char(string='File Name', required=True)
    file_data = fields.Binary(string='Upload File', required=True)
    product_template_coding_attribute_ids = fields.One2many('product.template.coding.attribute', 'attribute_import_id',
                                                            string="Coding Attribute Selection List", readonly=True)

    def generate_attributes(self):

        if self.product_template_coding_attribute_ids:
            raise UserError(_('product_template_coding_attribute_ids Already exists '))

        if self.file_data:
            try:
                excel_file = base64.b64decode(self.file_data)
                df = pd.read_excel(excel_file, engine='openpyxl', dtype={
                    '厂商代码': str,
                    '芯厚': str,
                    '胶厚': str,
                    '玻璃布': str,
                    '含胶量': str,
                })
            except:
                raise UserError(_('excel_file Reading failed'))

            if '芯板结构' in df.columns:
                for index, row in df.iterrows():
                    coding = row['物料编码']
                    tml_id = self.env['product.template'].search([('default_code', '=', coding)], limit=1)
                    if tml_id:
                        # 编码供应商
                        supplier_code = row['厂商代码']
                        if supplier_code:
                            if supplier_code == '00132':
                                supplier_code = '60018'
                            supplier_id = self.env['res.partner'].search([('code', '=', supplier_code)], limit=1)
                            if supplier_id:
                                tml_id.coding_provider_id = supplier_id.id

                            else:
                                raise UserError(_(str(supplier_code) + ' The supplier_code not found.'))
                        # 型号
                        brand = row['型号']
                        if brand:
                            tml_id.specification_eng = brand

                        attribute_ids_s = []

                        # 是否含铜
                        incldcu = row['是否含铜']
                        if incldcu:
                            coding_at_1 = self.env['sb.material.coding.attribute'].search([('code', '=', 'JCTYPE')], limit=1)
                            if coding_at_1:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_1.id),
                                     ('value_code', '=', incldcu)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_1.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(incldcu +' Code in JCTYPE not found.'))
                            else:
                                raise UserError(_('JCTYPE code not found.'))

                        # 芯厚
                        corethk = row['芯厚']
                        if corethk:
                            coding_at_2 = self.env['sb.material.coding.attribute'].search([('code', '=', 'JCHOU')],
                                                                                        limit=1)
                            if coding_at_2:
                                vals = {
                                    'product_template_id': tml_id.id,
                                    'attribute_import_id': self.id,
                                    'sb_material_coding_attribute_id': coding_at_2.id,
                                    'input_value': corethk,
                                    'coding_attribute_type': 'input',
                                    'coding_participation': True
                                }
                                attribute_ids_s.append((0, 0, vals))
                            else:
                                raise UserError(_('JCHOU code not found.'))
                        # 介厚
                        dielthk = row['介厚']
                        if dielthk:
                            coding_at_3 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'DielThk')],
                                limit=1)
                            if coding_at_3:
                                vals = {
                                    'product_template_id': tml_id.id,
                                    'attribute_import_id': self.id,
                                    'sb_material_coding_attribute_id': coding_at_3.id,
                                    'input_value': dielthk,
                                    'coding_attribute_type': 'input'
                                }
                                attribute_ids_s.append((0, 0, vals))
                            else:
                                raise UserError(_('DielThk code not found.'))

                        # ↑铜(oz)
                        cu = row['↑铜(oz)']
                        if cu:
                            coding_at_4 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'JCTHT')], limit=1)
                            if coding_at_4:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_4.id),
                                     ('value_code', '=', cu)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_4.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(cu + ' Code in JCTHT not found.'))
                            else:
                                raise UserError(_('JCTHT code not found.'))

                        # ↓铜(oz)
                        cux = row['↓铜(oz)']
                        if cux:
                            coding_at_5 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'JCTHB')], limit=1)
                            if coding_at_5:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_5.id),
                                     ('value_code', '=', cux)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_5.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(cux + ' Code in JCTHB not found.'))
                            else:
                                raise UserError(_('JCTHB code not found.'))

                        # 铜箔类型
                        cutype = row['铜箔类型']
                        if cutype:
                            coding_at_6 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'BLLX')], limit=1)
                            if coding_at_6:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_6.id),
                                     ('name', '=', cutype)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_6.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(cutype + ' Code in BLLX not found.'))
                            else:
                                raise UserError(_('BLLX code not found.'))

                        # 胶厚
                        adhthk = row['胶厚']
                        if adhthk:
                            coding_at_7 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'JH')], limit=1)
                            if coding_at_7:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_7.id),
                                     ('value_code', '=', adhthk)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_7.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(adhthk + ' Code in JH not found.'))
                            else:
                                raise UserError(_('JH code not found.'))

                        # 公差等级
                        tolclass = row['公差等级']
                        if tolclass:
                            coding_at_8 = self.env['sb.material.coding.attribute'].search(
                                [('code', '=', 'CORET')], limit=1)
                            if coding_at_8:
                                val = self.env['sb.material.coding.attribute.value'].search(
                                    [('material_coding_attribute_id', '=', coding_at_8.id),
                                     ('value_code', '=', tolclass)], limit=1)
                                if val:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_8.id,
                                        'material_coding_attribute_value_id': val.id,
                                        'coding_attribute_type': 'value',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_(tolclass + ' Code in CORET not found.'))
                            else:
                                raise UserError(_('CORET code not found.'))

                            # Core公差
                            coretol = row['Core公差']
                            if coretol:
                                coding_at_9 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'COREV')], limit=1)
                                if coding_at_9:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_9.id),
                                         ('name', '=', coretol)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_9.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(coretol + ' Code in COREV not found.'))
                                else:
                                    raise UserError(_('COREV code not found.'))
                            # 卤素
                            halogen = row['卤素']
                            if halogen:
                                coding_at_10 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'LS')], limit=1)
                                if coding_at_10:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_10.id),
                                         ('name', '=', halogen)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_10.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(halogen + ' Code in LS not found.'))
                                else:
                                    raise UserError(_('LS code not found.'))

                            # 芯板结构
                            corestructure = row['芯板结构']
                            if corestructure:
                                coding_at_11 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'XBJG')],
                                    limit=1)
                                if coding_at_11:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_at_11.id,
                                        'input_value': corestructure,
                                        'coding_attribute_type': 'plate_structure',
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_('XBJG code not found.'))

                            # 尺寸
                            size = row['尺寸']
                            if size:
                                coding_at_12 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'JCSHEET')], limit=1)
                                if coding_at_12:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_12.id),
                                         ('name', '=', size)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_12.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(size + ' Code in JCSHEET not found.'))
                                else:
                                    raise UserError(_('JCSHEET code not found.'))

                            # 芯板颜色
                            corecolor = row['芯板颜色']
                            if corecolor:
                                coding_at_13 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'XS')], limit=1)
                                if coding_at_13:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_13.id),
                                         ('name', '=', corecolor)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_13.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(corecolor + ' Code in XS not found.'))
                                else:
                                    raise UserError(_('XS code not found.'))

                            # 水印
                            logo = row['水印']
                            if logo:
                                coding_at_14 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'LOGO')], limit=1)
                                if coding_at_14:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_14.id),
                                         ('name_en', '=', logo)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_14.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(logo + '  in LOGO not found.'))
                                else:
                                    raise UserError(_('LOGO code not found.'))

                            # 备用
                            resrv = row['备用']
                            if resrv:
                                coding_at_15 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'BY')], limit=1)
                                if coding_at_15:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_at_15.id),
                                         ('name', '=', resrv)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_at_15.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(resrv + ' in BY not found.'))
                                else:
                                    raise UserError(_('BY code not found.'))

                        # 生成属性
                        if attribute_ids_s:
                            res = tml_id.write({
                                        'product_template_coding_attribute_ids': attribute_ids_s
                                    })
                            if not res:
                                raise UserError(_('write product abnormality'))

            elif '玻璃布' in df.columns:

                for index, row in df.iterrows():
                    coding = row['物料编码']
                    tml_id = self.env['product.template'].search([('default_code', '=', coding)], limit=1)
                    if tml_id:
                        # 编码供应商
                        supplier_code = row['厂商代码']
                        if supplier_code:
                            if supplier_code == '00132':
                                supplier_code = '60018'
                            supplier_id = self.env['res.partner'].search([('code', '=', supplier_code)], limit=1)
                            if supplier_id:
                                tml_id.coding_provider_id = supplier_id.id

                            else:
                                raise UserError(_(str(supplier_code) + ' The supplier_code not found.'))
                        # 型号
                        brand = row['型号']
                        if brand:
                            tml_id.specification_eng = brand

                        attribute_ids_s = []

                        # 玻璃布
                        glass = row['玻璃布']
                        if glass:
                            coding_bo_1 = self.env['sb.material.coding.attribute'].search([('code', '=', 'BLBZ')],
                                                                                          limit=1)
                            if coding_bo_1:
                                vals = {
                                    'product_template_id': tml_id.id,
                                    'attribute_import_id': self.id,
                                    'sb_material_coding_attribute_id': coding_bo_1.id,
                                    'input_value': glass,
                                    'coding_attribute_type': 'input',
                                    'coding_participation': True
                                }
                                attribute_ids_s.append((0, 0, vals))
                            else:
                                raise UserError(_('BLBZ code not found.'))

                            # 含胶量
                            rc = row['含胶量']
                            if rc:
                                coding_bo_2 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'HJL')],
                                    limit=1)
                                if coding_bo_2:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_bo_2.id,
                                        'input_value': rc,
                                        'coding_attribute_type': 'input',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_('HJL code not found.'))

                            # 厚度(μm)
                            ppthk = row['厚度(μm)']
                            if ppthk:
                                coding_bo_3 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'PPHD')],
                                    limit=1)
                                if coding_bo_3:
                                    vals = {
                                        'product_template_id': tml_id.id,
                                        'attribute_import_id': self.id,
                                        'sb_material_coding_attribute_id': coding_bo_3.id,
                                        'input_value': ppthk,
                                        'coding_attribute_type': 'input',
                                        'coding_participation': True
                                    }
                                    attribute_ids_s.append((0, 0, vals))
                                else:
                                    raise UserError(_('PPHD code not found.'))

                            # 卤素
                            halogen = row['卤素']
                            if halogen:
                                coding_bo_4 = self.env['sb.material.coding.attribute'].search(
                                    [('code', '=', 'LS')], limit=1)
                                if coding_bo_4:
                                    val = self.env['sb.material.coding.attribute.value'].search(
                                        [('material_coding_attribute_id', '=', coding_bo_4.id),
                                         ('name', '=', halogen)], limit=1)
                                    if val:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_bo_4.id,
                                            'material_coding_attribute_value_id': val.id,
                                            'coding_attribute_type': 'value',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_(halogen + ' Code in LS not found.'))
                                else:
                                    raise UserError(_('LS code not found.'))

                                # 纬向
                                fill = row['纬向']
                                if fill:
                                    coding_bo_5 = self.env['sb.material.coding.attribute'].search(
                                        [('code', '=', 'PPWX')],
                                        limit=1)
                                    if coding_bo_5:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_bo_5.id,
                                            'input_value': fill,
                                            'coding_attribute_type': 'input',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_('PPWX code not found.'))

                                # 经向
                                grain = row['经向']
                                if grain:
                                    coding_bo_6 = self.env['sb.material.coding.attribute'].search(
                                        [('code', '=', 'PPJX')],
                                        limit=1)
                                    if coding_bo_6:
                                        vals = {
                                            'product_template_id': tml_id.id,
                                            'attribute_import_id': self.id,
                                            'sb_material_coding_attribute_id': coding_bo_6.id,
                                            'input_value': grain,
                                            'coding_attribute_type': 'input',
                                            'coding_participation': True
                                        }
                                        attribute_ids_s.append((0, 0, vals))
                                    else:
                                        raise UserError(_('PPJX code not found.'))

                                # 单位
                                unit = row['单位']
                                if unit:
                                    coding_bo_10 = self.env['sb.material.coding.attribute'].search(
                                        [('code', '=', 'PPDW')], limit=1)
                                    if coding_bo_10:
                                        val = self.env['sb.material.coding.attribute.value'].search(
                                            [('material_coding_attribute_id', '=', coding_bo_10.id),
                                             ('name', '=', unit)], limit=1)
                                        if val:
                                            vals = {
                                                'product_template_id': tml_id.id,
                                                'attribute_import_id': self.id,
                                                'sb_material_coding_attribute_id': coding_bo_10.id,
                                                'material_coding_attribute_value_id': val.id,
                                                'coding_attribute_type': 'value',
                                                'coding_participation': True
                                            }
                                            attribute_ids_s.append((0, 0, vals))
                                        else:
                                            raise UserError(_(unit + 'in PPDW not found.'))
                                    else:
                                        raise UserError(_('PPDW code not found.'))

                                # 流胶情况
                                flow = row['流胶情况']
                                if flow:
                                    coding_bo_11 = self.env['sb.material.coding.attribute'].search(
                                        [('code', '=', 'PPLJ')], limit=1)
                                    if coding_bo_11:
                                        val = self.env['sb.material.coding.attribute.value'].search(
                                            [('material_coding_attribute_id', '=', coding_bo_11.id),
                                             ('name_en', '=', flow)], limit=1)
                                        if val:
                                            vals = {
                                                'product_template_id': tml_id.id,
                                                'attribute_import_id': self.id,
                                                'sb_material_coding_attribute_id': coding_bo_11.id,
                                                'material_coding_attribute_value_id': val.id,
                                                'coding_attribute_type': 'value',
                                                'coding_participation': True
                                            }
                                            attribute_ids_s.append((0, 0, vals))
                                        else:
                                            raise UserError(_(flow + 'in PPLJ not found.'))
                                    else:
                                        raise UserError(_('PPLJ code not found.'))

                                # 备用
                                resrv = row['备用']
                                if resrv:
                                    coding_bo_9 = self.env['sb.material.coding.attribute'].search(
                                        [('code', '=', 'BY')], limit=1)
                                    if coding_bo_9:
                                        val = self.env['sb.material.coding.attribute.value'].search(
                                            [('material_coding_attribute_id', '=', coding_bo_9.id),
                                             ('name', '=', resrv)], limit=1)
                                        if val:
                                            vals = {
                                                'product_template_id': tml_id.id,
                                                'attribute_import_id': self.id,
                                                'sb_material_coding_attribute_id': coding_bo_9.id,
                                                'material_coding_attribute_value_id': val.id,
                                                'coding_attribute_type': 'value',
                                                'coding_participation': True
                                            }
                                            attribute_ids_s.append((0, 0, vals))
                                        else:
                                            raise UserError(_(resrv + ' BY in XS not found.'))
                                    else:
                                        raise UserError(_('BY code not found.'))

                        # 生成属性
                        if attribute_ids_s:
                            res = tml_id.write({
                                'product_template_coding_attribute_ids': attribute_ids_s
                            })
                            if not res:
                                raise UserError(_('write product abnormality'))


            else:
                raise UserError(f"Column not found.")
        else:
            raise UserError("No file data available.")






